<?php
namespace Home\Controller;
use Think\Controller;
class IndexController extends Controller {
    public function index(){
        $this->show('<style type="text/css">*{ padding: 0; margin: 0; } div{ padding: 4px 48px;} body{ background: #fff; font-family: "微软雅黑"; color: #333;font-size:24px} h1{ font-size: 100px; font-weight: normal; margin-bottom: 12px; } p{ line-height: 1.8em; font-size: 36px } a,a:hover{color:blue;}</style><div style="padding: 24px 48px;"> <h1>:)</h1><p>欢迎使用 <b>ThinkPHP</b>！</p><br/>版本 V{$Think.version}</div><script type="text/javascript" src="http://ad.topthink.com/Public/static/client.js"></script><thinkad id="ad_55e75dfae343f5a1"></thinkad><script type="text/javascript" src="http://tajs.qq.com/stats?sId=9347272" charset="UTF-8"></script>','utf-8');
    }

    public $dsn  = "sqlsrv:Server=192.168.200.225;Database=nzg";
    public $user = "SA";
    public $pwd  = "123456";

    // public $dsn  = "sqlsrv:Server=127.0.0.1,1444;Database=nzg";
    // public $user = "SA";
    // public $pwd  = "Fgl88322";

    public function install(){

        $tables = S('tables') ;
        if( $tables===false ){
            $db     = M();
            $tables = $this->getTables($db);
        }

        // Chinese_PRC_CS_AI_WS  中文字符集
        $db = new \PDO( $this->dsn , $this->user , $this->pwd );
        $db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        //$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        $pcArr = [
            //'qii_apps'
            //'qii_actionlog_tiny' , 'qii_admin_menu'
            //'qii_article_type_archives'
        ];

        // 循环每一个表
        $i = 1 ;
        foreach ($tables as $tablename ) {
            //var_dump( $tablename );
            // 读出当前表数据
            if( !empty($pcArr) ){
                $data = [] ;
                if( in_array($tablename,$pcArr) ){
                    // ->where(['id'=>162])
                    $data = M( $tablename , null )->limit(1)->select();
                }
            }else{
                $data = M( $tablename , null )->select();
            }

            $data = $data; // array_filter
            if( !empty( $data ) ){
                $this->sqlsrvInsert($tablename,$data,$db);
            }
            $i = $i + 1 ;
            if($i==4){
                //die($i);
            }
        }
    }


    // 插入操作
    public function sqlsrvInsert($tableName='',$values=[],$db=[]) {
        try {

            if( empty($db) ){
                $db = new \PDO( $this->dsn , $this->user , $this->pwd );
            }

            // 清空表
            $sql= "TRUNCATE TABLE {$tableName};" ;
            $db->query($sql);

            // 将所有数据整合
            $newValues = $this->datazh($tableName,$values,$db);

            $tableName  = $tableName ? $tableName : 'qii_actionlog_tiny' ;
            $fields     = $this->sqlsrvGetFields($tableName,$db) ;

            $db->exec("SET IDENTITY_INSERT {$tableName} ON");
            foreach ($newValues as $key => $value) {

                $values = implode( ' , ' , $value ) ;
                // 每1000条插入一次
                $sql        = "INSERT INTO {$tableName} ({$fields}) VALUES {$values};" ;
                // print_r($str);

                if ( $count = $db->exec($sql) ) {
                    var_dump( '表插入:'.$tableName.' key:'.$key.' 插入:'.$count ) ;
                }else{
                    die("SET IDENTITY_INSERT {$tableName} ON;".$sql);
                }
            }
            $db->exec("SET IDENTITY_INSERT {$tableName} OFF");

        } catch (\PDOException $e) {
            print "Error: " . $e->getMessage() . "<br/>";
            die("SET IDENTITY_INSERT {$tableName} ON;".$sql);
        }
    }

    public function datazh($tableName='',$values=[],$db=[]){
        $fieldTypes = $this->sqlsrvGetFields($tableName,$db,false) ;
        $string = [
            'int identity' , 'smallint identity' , 'bigint identity' ,
            'smallint' , 'tinyint' , 'int' , 'bigint'
        ] ; // nvarchar ntext
        $newValues = [] ;
        foreach ($values as $key => $value) {
            $num = $key;
            if ($key==0) {
                $num = 1;
            }
            // 字段处理
            $newVal = [] ;
            foreach($value as $k => $v){
                if(  $fieldTypes[$k] == 'int identity'  ){
                    //var_dump($k.':'.$v); //主键
                }
                if( in_array( $fieldTypes[$k] , $string ) ){
                    if( empty($v) ){
                        $v = 0 ;
                    }
                }else{
                    if( empty($v) ){
                        $v = "N''" ;
                    }else{
                        if( $this->is_not_json($v) ){
                            $v = str_replace("'", "''", $v);
                        }else{
                            if( !is_numeric($v) ){
                                $arrData = json_decode($v,true); // addslashes
                                $v = json_encode($arrData,JSON_UNESCAPED_UNICODE);
                            }else{
                                $v = ''.$v ;
                            }
                            $v = str_replace("'", "''", $v);
                        }
                        $v = "N'".$v."'" ;
                    }
                }
                $newVal[] = $v ;
            }
            $value = '('.implode( ' , ' , $newVal).')';
            $newValues[ ceil( $num/999 ) ][$key] = $value ;
        }
        return $newValues ;
    }


    //编辑字段类型
    public function sqlsrvEditColumn() {
        try {

            $tables = S('tables') ;
            if( $tables===false ){
                $db     = M();
                $tables = $this->getTables($db);
            }

            $db = new \PDO( $this->dsn , $this->user , $this->pwd );
            $db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

            $pcArrs = [
                "qii_apps" => [
                    'config' => 'ntext' ,
                ] ,
                "qii_article_type_archives" => [
                    'content' => 'ntext' ,
                ]
            ];

            // 循环每一个表
            $sql = '';
            foreach ($tables as $tablename ) {
                // 读出当前表数据
                $tableInfo = $pcArrs[$tablename] ;
                if( $tableInfo ){
                    foreach ( $tableInfo as $name => $type ) {
                        $sql .= "Alter table [{$tablename}] Alter column [{$name}] [{$type}];" ;
                    }
                }
            }
            if($sql){
                $sql = $db->query($sql);
                // var_dump($sql);
            }
        } catch (\PDOException $e) {
            print "Error: " . $e->getMessage() . "<br/>";
            die();
        }
    }


    // 获取字段名称
    public function sqlsrvGetFields($tableName='',$db=[],$field=true,$type="sp_columns") {
        try {
            if( empty($db) ){
                $db = new \PDO( $this->dsn , $this->user , $this->pwd );
            }

            $tableName  = $tableName ? $tableName : 'qii_actionlog_tiny' ;
            $sql        = "{$type} {$tableName};" ; // sp_columns  sp_help

            $rs         = $db->query($sql);
            $result_arr = $rs->fetchAll();
            if( $field ){
                $arr        = array_column($result_arr,'COLUMN_NAME') ;
                $newData    = [] ;
                foreach($arr as $val ){
                    $newData[] = '['.$val.']';
                }
                $str        = implode(',',$newData);
                return $str ;
            }else{
                foreach($result_arr as $val ){
                    $newData[$val['COLUMN_NAME']  ] = $val['TYPE_NAME'] ;
                }
                return $newData ;
            }
        } catch (\PDOException $e) {
            print "Error: " . $e->getMessage() . "<br/>";
            die();
        }
    }

    public function sqlsrvSelect() {
        try {
            $db = new \PDO( $this->dsn , $this->user , $this->pwd );

            $tableName  = 'dbo.qii_actionlog_tiny' ;
            $fields     = '*' ; // id,source,user_id,data,modified,status
            $sql        = "SELECT {$fields} from {$tableName};" ;

            $rs         = $db->query($sql);
            $result_arr = $rs->fetchAll();
            var_dump($result_arr);

        } catch (\PDOException $e) {
            print "Error: " . $e->getMessage() . "<br/>";
            die();
        }
    }

    public function getTables($db=[],$dbName='') {
        $sql    = !empty($dbName)?'SHOW TABLES FROM '.$dbName:'SHOW TABLES ';
        $result = $db->query($sql);
        $info   =   array();
        foreach ($result as $key => $val) {
            $info[$key] = current($val);
        }
        return $info;
    }

    public function iconv2utf8($Result) {
        $Row = array();
        $key1=array_keys($Result);
        $key2 = array_keys($Result[$key1[0]]);
        for($i=0;$i<count($key1);$i++) {
            for( $j=0; $j<count( $key2 ); $j++ ) {
                $Row[ $key1[ $i ] ][ $key2[ $j ] ] = iconv( 'gb2312', 'utf-8', $Result[ $key1[ $i ] ][ $key2[ $j ] ] );
            }
        }
        return $Row;
    }

    //编辑字段类型
    public function sqlsrvText() {
        try {
            $db = new \PDO( $this->dsn , $this->user , $this->pwd );
            $db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
            // 循环每一个表
            $sql = '';

            $sql = "SELECT * from text;" ;
            if($sql){
                $rs     = $db->query($sql);
                $data   = $rs->fetchAll();
            }
            var_dump($data);
        } catch (\PDOException $e) {
            print "Error: " . $e->getMessage() . "<br/>";
            die();
        }
    }

    public  function iconv2gbk($string) {
        return iconv( 'utf-8','gb2312' ,$string );
    }

    public function is_not_json($str){
        return is_null(json_decode($str));
        $res = json_decode($value, true);
        $error = json_last_error();
        if ( empty($error) ) {
            return true ;
        }
        return false ;
    }

    public function is_serialized( $data, $strict = true ) {
        // if it isn't a string, it isn't serialized.
        if ( ! is_string( $data ) ) {
            return false;
        }
        $data = trim( $data );
        if ( 'N;' == $data ) {
            return true;
        }
        if ( strlen( $data ) < 4 ) {
            return false;
        }
        if ( ':' !== $data[1] ) {
            return false;
        }
        if ( $strict ) {
            $lastc = substr( $data, -1 );
            if ( ';' !== $lastc && '}' !== $lastc ) {
                return false;
            }
        } else {
            $semicolon = strpos( $data, ';' );
            $brace     = strpos( $data, '}' );
            // Either ; or } must exist.
            if ( false === $semicolon && false === $brace )
                return false;
            // But neither must be in the first X characters.
            if ( false !== $semicolon && $semicolon < 3 )
                return false;
            if ( false !== $brace && $brace < 4 )
                return false;
        }
        $token = $data[0];
        switch ( $token ) {
            case 's' :
                if ( $strict ) {
                    if ( '"' !== substr( $data, -2, 1 ) ) {
                        return false;
                    }
                } elseif ( false === strpos( $data, '"' ) ) {
                    return false;
                }
                // or else fall through
            case 'a' :
            case 'O' :
                return (bool) preg_match( "/^{$token}:[0-9]+:/s", $data );
            case 'b' :
            case 'i' :
            case 'd' :
                $end = $strict ? '$' : '';
                return (bool) preg_match( "/^{$token}:[0-9.E-]+;$end/", $data );
        }
        return false;
    }

    function text(){
        var_dump( is_null(json_decode('admin')) );
    }
}
